<?php

class Model_Report extends Model_Base
{
    public function getFesterTermin($week)
    {
	$week = $week - 1;
	$weekday = date("w", time());
	$timestamp_monday = time() - ($weekday-1)*60*60*24;
	$timestamp_sunday =  $timestamp_monday+6*60*60*24;
	
	$fromtmp = date("Y-M-d 00:00:00", ($timestamp_monday+(($week-1)*60*60*24*7)));
	$totmp = date("Y-M-d 00:00:00", ($timestamp_sunday+(($week-1)*60*60*24*7)));
	
	$from = new Zend_Date($fromtmp);
	$to = new Zend_Date($totmp);
	
	$select = $this->db->select()
	    ->distinct()
	    ->from(array('e' => 'event'))
	    ->where("e.event_type = 'Fester Termin'")
	    ->where('e.date_to >= ?', $from->toString('YYYY-MM-dd HH:mm:ss'))
	    ->where('e.date_from <= ?', $to->toString('YYYY-MM-dd HH:mm:ss'));

	return $this->db->fetchAll($select);
    }
    
    public function getAbgelehntDate($year)
    {
	
	$select = $this->db->select()
	    ->distinct()
	    ->from(array('e' => 'event'))
	    ->where("e.event_status = 'Abgelehnt'")
	    ->where("e.date_from >= ?", "$year-01-01 00:00:00")
	    ->where("e.date_to <= ?", "$year-12-31 23:59:59");
	return $this->db->fetchAll($select);
    }
    
    public function getSupervisedChildrenForWeek($week)
    {
	$weekday = date("w", time());
	$timestamp_monday = time() - ($weekday-1)*60*60*24;
	$timestamp_sunday =  $timestamp_monday+6*60*60*24;
	
	$from = date("Y-m-d 00:00:00", ($timestamp_monday+(($week-2)*60*60*24*7)));
	$to = date("Y-m-d 23:59:59", ($timestamp_sunday+(($week-2)*60*60*24*7)));
	
	$select = $this->db->select()
	    ->distinct()
	    ->from(array('p' => 'person'))
	    ->join(array('ep' => 'event_link_person'), 'p.id = ep.idperson', array())
	    ->join(array('e' => 'event'), 'e.id = ep.idevent', array())
	    ->where("e.event_status = 'Angenommen'")
	    ->where("p.person_type = 'Kind'")
	    ->where('e.date_from <= ?', $to)
	    ->where('e.date_from >= ?', $from);

	return $this->db->fetchAll($select);
	}
	
    public function getSupervisedEmployeeForWeek($week)
    {
	$weekday = date("w", time());
	$timestamp_monday = time() - ($weekday-1)*60*60*24;
	$timestamp_sunday =  $timestamp_monday+6*60*60*24;
	
	$from = date("Y-m-d 00:00:00", ($timestamp_monday+(($week-2)*60*60*24*7)));
	$to = date("Y-m-d 23:59:59", ($timestamp_sunday+(($week-2)*60*60*24*7)));
	
	$select = $this->db->select()
	    ->distinct()
	    ->from(array('p' => 'person'))
	    ->join(array('ep' => 'event_link_person'), 'p.id = ep.idperson', array())
	    ->join(array('e' => 'event'), 'e.id = ep.idevent', array())
	    //->where("e.event_status = 'Angenommen'")
	    ->where("p.person_type = 'Mitarbeiter'")
	    ->where('e.date_from <= ?', $to)
	    ->where('e.date_from >= ?', $from);

	return $this->db->fetchAll($select);
	}
    
    public function getAllEmployees()
    {
	$select = $this->db->select()
		->from(array('p' => 'person'))
		->where("p.person_type = 'Mitarbeiter'")
		->where("p.expire_date is null")
		->orWhere("DATE_FORMAT(p.expire_date, '%Y') = DATE_FORMAT(now(),'%Y')");
	
	return $this->db->fetchAll($select);
    }
    
    public function getAllEmployeesWithWorktimeForTimespan($id, Zend_Date $from, Zend_Date $to)
    {
	$select = $this->db->select() 
		->from(array('w' => 'worktime'), array('sum' => 'sum(UNIX_TIMESTAMP(w.date_to) - UNIX_TIMESTAMP(w.date_from))/3600'))
		->where("w.date_from Between '" . $from->toString('YYYY-MM-dd HH:mm:ss') . "' AND '" . $to->toString('YYYY-MM-dd HH:mm:ss'). "'")
		->where("w.date_to Between '" . $from->toString('YYYY-MM-dd HH:mm:ss') . "' AND '" . $to->toString('YYYY-MM-dd HH:mm:ss'). "'")
		->where('w.idperson = ?', $id)
		->where("w.worktime_type = 'worktime'");
	
	$result = $this->db->fetchRow($select);
	return $result['sum'];
    }
    
    public function getEmployeeMonthlyReportForYear($year)
    {
	$result = array();
	
	$user = $this->getAllEmployees();
	
	foreach ($user as $value)
	{
	    for($i = 1; $i <= 12; ++$i)
	    {
		$month = $i < 10 ? "0$i" : "$i";
		$from = new Zend_Date("$year-$month-01 00:00:00");
		$to = clone $from;
		$to->addMonth(1)->subSecond(1);
		$id = $value['id'];
		$result[$id][$month] = $this->getAllEmployeesWithWorktimeForTimespan($id, $from, $to);
	    }
	}
	
	return $result;
    }
    
    private function getAvaiableEmployeeForTimespan($from, $to)
    {
	$select = $this->db->select()
		->from(array('w' => 'worktime'), array('id' => 'p.id','vname' => 'p.forename', 'name' => 'p.surname' , 'from' => 'w.date_from', 'to' => 'w.date_to'))
		->join(array('p' => 'person'), 'p.id = w.idperson', array())
		->where("w.worktime_type = 'availability'")
		->where("w.date_from Between '" . $from->toString('YYYY-MM-dd HH:mm:ss') . "' AND '" . $to->toString('YYYY-MM-dd HH:mm:ss'). "'");
	
	return $this->db->fetchAll($select);
    }
    
    public function getAvailableEmployeeReportForWeek($week, $year)
    {
	$date = new Zend_Date();
	$date->set($year, Zend_Date::YEAR);
	$date->set($week, Zend_Date::WEEK);
	$currentWeek = $date->getDate();
	
	$start = $currentWeek->setWeekday(1);
	$end = clone $start;
	$end->addDay(5)->subSecond(1);
	
        $result = $this->getAvaiableEmployeeForTimespan($start, $end);
	
	return $result;
    }
    
    public function getSupervisedChildrenForTimespan(Zend_Date $from, Zend_Date $to)
    {
	$select = $this->db->select()
	    ->distinct()
	    ->from(array('p' => 'person'))
	    ->join(array('ep' => 'event_link_person'), 'p.id = ep.idperson', array())
	    ->join(array('e' => 'event'), 'e.id = ep.idevent', array())
	    ->where("e.event_status = 'Angenommen'")
	    ->where("p.person_type = 'Kind'")
	    ->where('e.date_to >= ?', $from->toString('YYYY-MM-dd HH:mm:ss'))
	    ->where('e.date_from <= ?', $to->toString('YYYY-MM-dd HH:mm:ss'));

	return $this->db->fetchAll($select);
    }
    
    public function getMonthlyReportForYear($year)
    {
	$result = array();
	for($i = 1; $i <= 12; ++$i)
	{
	    $month = $i < 10 ? "0$i" : "$i";
	    $from = new Zend_Date("$year-$month-01 00:00:00");
	    $to = clone $from;
	    $to->addMonth(1)->subSecond(1);
	    
	    $supervisedChildren = $this->getSupervisedChildrenForTimespan($from, $to);
	    $result[] = array(
		'children' => count($supervisedChildren),
		'ageAvg' => $this->getAverageAge($supervisedChildren),
		'singleDates' => $this->getEvents($from, $to, 'Einzeltermin'),
		'fixedDates' => $this->getEvents($from, $to, 'Fester Termin')
	    );
	}
	return $result;
    }
    
    public function getEvents(Zend_Date $from, Zend_Date $to, $type)
    {
	$select = $this->db->select()
	    ->from(array('e' => 'event'), array('count' => 'COUNT(*)'))
	    ->join(array('ep' => 'event_link_person'), 'e.id = ep.idevent', array())
	    ->join(array('p' => 'person'), 'p.id = ep.idperson', array())
	    ->where("e.event_status = 'Angenommen'")
	    ->where("e.event_type = '$type'")
	    ->where('e.date_to <= ?', $to->toString('YYYY-MM-dd HH:mm:ss'))
	    ->where('e.date_from >= ?', $from->toString('YYYY-MM-dd HH:mm:ss'))
	    ->where("p.person_type = 'Kind'");
	
	$result = $this->db->fetchRow($select);
	return $result['count'];
    }
    
    public function getAverageAge($children)
    {
	if(0 == count($children))
	{
	    return 0;
	}
	
	$ageSum = 0;
	foreach($children as $child)
	{
	    $ageSum += $this->getAge($child);
	}
	
	return (float)$ageSum / count($children);
    }
    
    public function getAge($child)
    {
	$birthday = new Zend_Date($child['birthday']);
	$now = new Zend_Date;
	$now->sub($birthday->get(Zend_Date::YEAR), Zend_Date::YEAR)
	    ->sub($birthday->get(Zend_Date::MONTH) - 1, Zend_Date::MONTH)
	    ->sub($birthday->get(Zend_Date::DAY), Zend_Date::DAY);
	return $now->get(Zend_Date::YEAR);
    }
}